package com.trkj.dao;

import com.trkj.entity.Account;
import com.trkj.entity.Transaction;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

@Repository
public class accountDao extends JdbcDaoSupport {
    public accountDao(DataSource druidDataSource){setDataSource(druidDataSource);}

    public int insertAccount(Account account){
        String sql="insert into account(name, password, gender, id_card, initial_amount, open_time, balance) values(?,?,?,?,?,?,?)";
        int count=this.getJdbcTemplate().update(sql,account.getName(),account.getPassword(),account.getGender(),account.getIdCard(),account.getInitialAmount(),account.getOpenTime(),account.getBalance());
        return count;
    }
    public boolean validatePassword(int accountId, String inputPassword) {
        String sql = "SELECT COUNT(*) FROM account WHERE id = ? AND password = ?";
        int count = this.getJdbcTemplate().queryForObject(sql, Integer.class, accountId, inputPassword);
        return count > 0;
    }
    public int updatePassword(int accountId, String newPassword, String oldPassword) {
        if (validatePassword(accountId, oldPassword)) {
            String updateSql = "UPDATE account SET password = ? WHERE id = ?";
            int count = this.getJdbcTemplate().update(updateSql, newPassword, accountId);
            return count;
        } else {
            // 原密码验证不通过，可以进行相应处理，比如抛出异常或返回特定的错误码
            return 0;
        }
    }
    public double getBalance(int accountId, String inputPassword) {
        String sql = "SELECT balance FROM account WHERE id = ? AND password = ?";
        double balance = this.getJdbcTemplate().queryForObject(sql, double.class, accountId, inputPassword);
        return balance;
    }
    public int updateYE(int id1,int id2,double je,String pass,String type){
        double ye=this.getBalance(id1,pass);
        if(je<=ye){
            String sql="UPDATE account SET balance = balance - ? WHERE id = ?";
            int count=this.getJdbcTemplate().update(sql,je,id1);

            String sql2="UPDATE account SET balance = balance + ? WHERE id = ?";
            int count2=this.getJdbcTemplate().update(sql2,je,id1);

            String sql3="INSERT INTO transaction (account_id, transaction_type, transaction_time, transaction_amount)\n" +
                    "VALUES (?,?, NOW(),?);";
            int count3=this.getJdbcTemplate().update(sql3,id1,type,je);

            String sql4="INSERT INTO transaction (account_id, transaction_type, transaction_time, transaction_amount)\n" +
                    "VALUES (?,?, NOW(),?);\n";
            int count4=this.getJdbcTemplate().update(sql4,id2,type,je);
            return count4;
        }else{
            return 0;
        }
    }
    public boolean dui() {
        String sql = "SELECT a.id, " +
                "       a.name, " +
                "       a.balance AS current_balance," +
                "       a.initial_amount + COALESCE(SUM(CASE WHEN t.transaction_type = 'DEPOSIT' THEN t.transaction_amount ELSE 0 END), 0) - COALESCE(SUM(CASE WHEN t.transaction_type = 'WITHDRAWAL' THEN t.transaction_amount ELSE 0 END), 0) AS calculated_balance " +
                "FROM account a " +
                "LEFT JOIN transaction t ON a.id = t.account_id " +
                "GROUP BY a.id, a.name, a.initial_amount, a.balance";

        List<Map<String, Object>> resultList = this.getJdbcTemplate().queryForList(sql);

        // 处理查询结果
        for (Map<String, Object> row : resultList) {
            // 可以在这里进行结果处理，例如打印或者其他操作
            System.out.println(row);
        }

        return true; // 根据实际情况返回值
    }
    public List<Map<String,Object>> selectZh(int id){
        String sql="SELECT *\n" +
                "FROM transaction\n" +
                "WHERE account_id =?;";
        List<Map<String,Object>> list=this.getJdbcTemplate().queryForList(sql,id);
        return list;
    }
}
